
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 12/28/2010 21:11:01
-- Generated from EDMX file: C:\Users\10817\Documents\Visual Studio 2010\Projects\helpcentral\HelpCentral.Data\Model\HelpCentralModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [Helpdesk];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_UserAuditEvent]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[AuditEvents] DROP CONSTRAINT [FK_UserAuditEvent];
GO
IF OBJECT_ID(N'[dbo].[FK_ClientAuditEvent]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[AuditEvents] DROP CONSTRAINT [FK_ClientAuditEvent];
GO
IF OBJECT_ID(N'[dbo].[FK_ClientUser]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[User] DROP CONSTRAINT [FK_ClientUser];
GO
IF OBJECT_ID(N'[dbo].[FK_ClientQuestionGroup]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[QuestionGroups] DROP CONSTRAINT [FK_ClientQuestionGroup];
GO
IF OBJECT_ID(N'[dbo].[FK_QuestionGroupQuestion]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Questions] DROP CONSTRAINT [FK_QuestionGroupQuestion];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Clients]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Clients];
GO
IF OBJECT_ID(N'[dbo].[User]', 'U') IS NOT NULL
    DROP TABLE [dbo].[User];
GO
IF OBJECT_ID(N'[dbo].[AuditEvents]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AuditEvents];
GO
IF OBJECT_ID(N'[dbo].[Questions]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Questions];
GO
IF OBJECT_ID(N'[dbo].[Tickets]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Tickets];
GO
IF OBJECT_ID(N'[dbo].[ValidResponses]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ValidResponses];
GO
IF OBJECT_ID(N'[dbo].[TicketResponses]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TicketResponses];
GO
IF OBJECT_ID(N'[dbo].[QuestionGroups]', 'U') IS NOT NULL
    DROP TABLE [dbo].[QuestionGroups];
GO
IF OBJECT_ID(N'[dbo].[UserAccessTokens]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserAccessTokens];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Clients'
CREATE TABLE [dbo].[Clients] (
    [ClientId] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(100)  NOT NULL,
    [License] uniqueidentifier  NOT NULL,
    [ActivateDate] datetime  NOT NULL,
    [DeactivateDate] datetime  NULL
);
GO

-- Creating table 'User'
CREATE TABLE [dbo].[User] (
    [UserId] int IDENTITY(1,1) NOT NULL,
    [Username] nvarchar(40)  NOT NULL,
    [PasswordHash] varbinary(32)  NOT NULL,
    [ADUsername] nvarchar(255)  NULL,
    [ActivateDate] datetime  NOT NULL,
    [DeactivateDate] datetime  NULL,
    [PasswordExpirationDate] datetime  NOT NULL,
    [Client_ClientId] int  NULL
);
GO

-- Creating table 'AuditEvents'
CREATE TABLE [dbo].[AuditEvents] (
    [AuditId] int IDENTITY(1,1) NOT NULL,
    [EventType] nvarchar(255)  NOT NULL,
    [Information] nvarchar(max)  NULL,
    [Date] datetime  NOT NULL,
    [AffectedObjectId] int  NULL,
    [SecondAffectedObjectId] int  NULL,
    [User_UserId] int  NULL,
    [Client_ClientId] int  NULL
);
GO

-- Creating table 'Questions'
CREATE TABLE [dbo].[Questions] (
    [QuestionId] int IDENTITY(1,1) NOT NULL,
    [Type] nvarchar(50)  NOT NULL,
    [QuestionText] nvarchar(max)  NOT NULL,
    [ActivateDate] datetime  NOT NULL,
    [DeactivateDate] datetime  NULL,
    [Order] int  NOT NULL,
    [QuestionGroup_QuestionGroupId] int  NOT NULL
);
GO

-- Creating table 'Tickets'
CREATE TABLE [dbo].[Tickets] (
    [TicketId] int IDENTITY(1,1) NOT NULL,
    [CreatedDate] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'ValidResponses'
CREATE TABLE [dbo].[ValidResponses] (
    [ValidResponseId] int IDENTITY(1,1) NOT NULL,
    [QuestionId] int  NOT NULL,
    [Response] nvarchar(max)  NOT NULL,
    [ActivateDate] datetime  NOT NULL,
    [DeactivateDate] datetime  NULL
);
GO

-- Creating table 'TicketResponses'
CREATE TABLE [dbo].[TicketResponses] (
    [TicketResponseId] int IDENTITY(1,1) NOT NULL,
    [TicketId] nvarchar(max)  NOT NULL,
    [Order] int  NOT NULL,
    [QuestionText] nvarchar(max)  NOT NULL,
    [Response] nvarchar(max)  NOT NULL,
    [QuestionId] nvarchar(max)  NOT NULL,
    [ValidResponseId] int  NULL
);
GO

-- Creating table 'QuestionGroups'
CREATE TABLE [dbo].[QuestionGroups] (
    [QuestionGroupId] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Client_ClientId] int  NOT NULL
);
GO

-- Creating table 'UserAccessTokens'
CREATE TABLE [dbo].[UserAccessTokens] (
    [UserAccessTokenId] uniqueidentifier  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [ClientId] in table 'Clients'
ALTER TABLE [dbo].[Clients]
ADD CONSTRAINT [PK_Clients]
    PRIMARY KEY CLUSTERED ([ClientId] ASC);
GO

-- Creating primary key on [UserId] in table 'User'
ALTER TABLE [dbo].[User]
ADD CONSTRAINT [PK_User]
    PRIMARY KEY CLUSTERED ([UserId] ASC);
GO

-- Creating primary key on [AuditId] in table 'AuditEvents'
ALTER TABLE [dbo].[AuditEvents]
ADD CONSTRAINT [PK_AuditEvents]
    PRIMARY KEY CLUSTERED ([AuditId] ASC);
GO

-- Creating primary key on [QuestionId] in table 'Questions'
ALTER TABLE [dbo].[Questions]
ADD CONSTRAINT [PK_Questions]
    PRIMARY KEY CLUSTERED ([QuestionId] ASC);
GO

-- Creating primary key on [TicketId] in table 'Tickets'
ALTER TABLE [dbo].[Tickets]
ADD CONSTRAINT [PK_Tickets]
    PRIMARY KEY CLUSTERED ([TicketId] ASC);
GO

-- Creating primary key on [ValidResponseId] in table 'ValidResponses'
ALTER TABLE [dbo].[ValidResponses]
ADD CONSTRAINT [PK_ValidResponses]
    PRIMARY KEY CLUSTERED ([ValidResponseId] ASC);
GO

-- Creating primary key on [TicketResponseId] in table 'TicketResponses'
ALTER TABLE [dbo].[TicketResponses]
ADD CONSTRAINT [PK_TicketResponses]
    PRIMARY KEY CLUSTERED ([TicketResponseId] ASC);
GO

-- Creating primary key on [QuestionGroupId] in table 'QuestionGroups'
ALTER TABLE [dbo].[QuestionGroups]
ADD CONSTRAINT [PK_QuestionGroups]
    PRIMARY KEY CLUSTERED ([QuestionGroupId] ASC);
GO

-- Creating primary key on [UserAccessTokenId] in table 'UserAccessTokens'
ALTER TABLE [dbo].[UserAccessTokens]
ADD CONSTRAINT [PK_UserAccessTokens]
    PRIMARY KEY CLUSTERED ([UserAccessTokenId] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [User_UserId] in table 'AuditEvents'
ALTER TABLE [dbo].[AuditEvents]
ADD CONSTRAINT [FK_UserAuditEvent]
    FOREIGN KEY ([User_UserId])
    REFERENCES [dbo].[User]
        ([UserId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserAuditEvent'
CREATE INDEX [IX_FK_UserAuditEvent]
ON [dbo].[AuditEvents]
    ([User_UserId]);
GO

-- Creating foreign key on [Client_ClientId] in table 'AuditEvents'
ALTER TABLE [dbo].[AuditEvents]
ADD CONSTRAINT [FK_ClientAuditEvent]
    FOREIGN KEY ([Client_ClientId])
    REFERENCES [dbo].[Clients]
        ([ClientId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ClientAuditEvent'
CREATE INDEX [IX_FK_ClientAuditEvent]
ON [dbo].[AuditEvents]
    ([Client_ClientId]);
GO

-- Creating foreign key on [Client_ClientId] in table 'User'
ALTER TABLE [dbo].[User]
ADD CONSTRAINT [FK_ClientUser]
    FOREIGN KEY ([Client_ClientId])
    REFERENCES [dbo].[Clients]
        ([ClientId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ClientUser'
CREATE INDEX [IX_FK_ClientUser]
ON [dbo].[User]
    ([Client_ClientId]);
GO

-- Creating foreign key on [Client_ClientId] in table 'QuestionGroups'
ALTER TABLE [dbo].[QuestionGroups]
ADD CONSTRAINT [FK_ClientQuestionGroup]
    FOREIGN KEY ([Client_ClientId])
    REFERENCES [dbo].[Clients]
        ([ClientId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ClientQuestionGroup'
CREATE INDEX [IX_FK_ClientQuestionGroup]
ON [dbo].[QuestionGroups]
    ([Client_ClientId]);
GO

-- Creating foreign key on [QuestionGroup_QuestionGroupId] in table 'Questions'
ALTER TABLE [dbo].[Questions]
ADD CONSTRAINT [FK_QuestionGroupQuestion]
    FOREIGN KEY ([QuestionGroup_QuestionGroupId])
    REFERENCES [dbo].[QuestionGroups]
        ([QuestionGroupId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_QuestionGroupQuestion'
CREATE INDEX [IX_FK_QuestionGroupQuestion]
ON [dbo].[Questions]
    ([QuestionGroup_QuestionGroupId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------